package com.kfm.jdbc.day0403;

import com.kfm.jdbc.day0328.DBUtils;
import com.kfm.jdbc.day0328.entity.Goods;
import org.apache.poi.hssf.extractor.ExcelExtractor;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

public class Demo05 {


    public static void main(String[] args) {
//        createBaseExcel();
        try (InputStream inp = new FileInputStream("poi/base.xls")) {
            HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
            ExcelExtractor extractor = new ExcelExtractor(wb);
            extractor.setFormulasNotResults(true);
            extractor.setIncludeSheetNames(false);

            HSSFWorkbook document = extractor.getDocument();
            HSSFSheet sheetAt = document.getSheetAt(0);

            int max = sheetAt.getLastRowNum();
            List<Goods> list = new ArrayList<>();
            for (int i = 1; i <= max; i ++){
                HSSFRow row = sheetAt.getRow(i);
                int maxCell = row.getLastCellNum();

                Goods goods = new Goods();
                goods.setGoodsName(row.getCell(0).getStringCellValue());
                goods.setCost(row.getCell(1).getNumericCellValue());
                goods.setPrice(row.getCell(2).getNumericCellValue());
                goods.setCount((int)row.getCell(3).getNumericCellValue());
                goods.setCreateTime(row.getCell(4).getDateCellValue());
                goods.setCategoryNo((int)row.getCell(5).getNumericCellValue());

                list.add(goods);
            }

            // 批量插入数据库
            DBUtils<Goods> dbUtils = new DBUtils<>(1);
            String sql = "insert into goods(goods_name, cost, price, count, create_time, category_no) values(?, ?, ?, ?, ?, ?)";
            dbUtils.insertBatch(sql, list);
            wb.close();
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }


    static void createBaseExcel(){
        HSSFWorkbook sheets = new HSSFWorkbook();

        HSSFSheet sheet = sheets.createSheet("自定义sheet");

        int rowIndex= 0;
        // 创建行
        HSSFRow row = sheet.createRow(rowIndex ++);
        row.createCell(0).setCellValue("商品名称");
        row.createCell(1).setCellValue("商品进价");
        row.createCell(2).setCellValue("商品售价");
        row.createCell(3).setCellValue("商品库存");
        row.createCell(4).setCellValue("入库时间");
        row.createCell(5).setCellValue("分类编号");

        try (OutputStream fileOut = new FileOutputStream("poi/base.xls")) {
            sheets.write(fileOut);
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
}
